-- =============================================
-- Author:        Paul Bradshaw
-- Create date: Jan 30th, 2007
-- Description:    Create the Name_All View
-- =============================================
CREATE PROCEDURE [dbo].[asi_BuildNameAllView]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @rowctr int
    DECLARE @sql1 varchar(max)
    DECLARE @sql2 varchar(max)
    DECLARE @sql3 varchar(max)
    DECLARE @tmpstr varchar(max)
    DECLARE @tmpstr2 varchar(max)
    DECLARE @temp_table table (table_name sysname, col_name sysname null)
    DECLARE @table_name sysname
    DECLARE @col_name sysname
    DECLARE @prev_col_name sysname
    DECLARE @CRLF varchar(3)
    SET @CRLF = char(13) + char(10)

    INSERT INTO @temp_table
        SELECT udf.TABLE_NAME, udf.FIELD_NAME
          FROM UD_Field udf  
               INNER JOIN UD_Table udt ON udf.TABLE_NAME = udt.TABLE_NAME
         WHERE udt.ALLOW_MULTIPLE_INSTANCES = 0 AND udt.NAME_ALL_TABLE = 1
               AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = udf.TABLE_NAME AND COLUMN_NAME = udf.FIELD_NAME)
               
    INSERT INTO @temp_table
        SELECT 'Name', COLUMN_NAME
          FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_NAME = 'Name' and COLUMN_NAME <> 'TIME_STAMP'

    INSERT INTO @temp_table
        SELECT 'Name_Fin', COLUMN_NAME
          FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_NAME = 'Name_Fin' and COLUMN_NAME <> 'ID' and COLUMN_NAME <> 'TIME_STAMP'

    -- should always be at least one column, so no error checking
    SELECT @sql1 = 'CREATE VIEW Name_All (' + @CRLF

    SELECT @tmpstr = ''
    SELECT @prev_col_name = ''
     
    DECLARE col_cursor CURSOR FAST_FORWARD FOR SELECT table_name, col_name FROM @temp_table ORDER BY col_name
    OPEN col_cursor
    FETCH next FROM col_cursor INTO @table_name, @col_name
    WHILE @@FETCH_STATUS = 0
        BEGIN    
        IF @col_name <> @prev_col_name
            BEGIN
                SELECT @sql1 = @sql1 +  @col_name + ', '
                SELECT @tmpstr = @tmpstr + @table_name + '.' + @col_name + ', '
            END
        SET @prev_col_name = @col_name
        FETCH next FROM col_cursor INTO @table_name, @col_name
        END
    CLOSE col_cursor
    DEALLOCATE col_cursor

    SELECT @sql1 = left(@sql1, datalength (@sql1) - 2)
    SELECT @tmpstr = left(@tmpstr, datalength (@tmpstr) - 2)

    SELECT @sql1 = @sql1 + @CRLF + ') AS ' + @CRLF
    SELECT @sql2 = 'SELECT ' + @tmpstr + @CRLF
    SELECT @sql3 = '  FROM '

    DELETE FROM @temp_table

    INSERT INTO @temp_table (table_name)
        SELECT DISTINCT udf.TABLE_NAME
          FROM UD_Field udf  
               INNER JOIN UD_Table udt ON udf.TABLE_NAME = udt.TABLE_NAME
         WHERE udt.NAME_ALL_TABLE = 1
               AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = udf.TABLE_NAME AND COLUMN_NAME = udf.FIELD_NAME)

    SELECT @rowctr = count(1) FROM @temp_table
    IF @rowctr = 0  -- no UD Dues billing tables
        BEGIN
        SELECT @sql3 = @sql3 + 'Name, Name_Fin' + @CRLF + 'where Name.ID = Name_Fin.ID'
        END
    ELSE
        BEGIN
        SELECT @tmpstr = ''
        SELECT @tmpstr2 = ''

        DECLARE UD_cursor CURSOR FAST_FORWARD FOR SELECT table_name FROM @temp_table ORDER BY table_name
        OPEN UD_cursor
        FETCH next FROM UD_cursor INTO @table_name
        WHILE @@FETCH_STATUS = 0
            BEGIN    
            SELECT @tmpstr = @tmpstr + @table_name + ', '
            SELECT @tmpstr2 = @tmpstr2 + ' Name.ID = ' + @table_name + '.ID and '
            FETCH next FROM UD_cursor INTO @table_name
            END
        CLOSE UD_cursor
        DEALLOCATE UD_cursor
    
        SELECT @sql3 = @sql3 + 'Name, '
        SELECT @sql3 = @sql3 + @tmpstr
        SELECT @sql3 = @sql3 + 'Name_Fin' + @CRLF + 'where'
        SELECT @sql3 = @sql3 + @tmpstr2
        SELECT @sql3 = @sql3 + 'Name.ID = Name_Fin.ID'
        END

    EXEC (@sql1 + @sql2 + @sql3)
END


GO